package com.wxf.dao.impl;

import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.wxf.bean.DBUtils;
import com.wxf.bean.Student;
import com.wxf.dao.StudentDao;
import com.wxf.util.StudentStateEnum;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudentList(String stuName, String stuNo, int sex, int pageIndex,int pageNum) {
        // 要使用模糊查询
        // 1-如果客户端传过来的请求参数为空，则为全查，否则根据传入的参数查询
        StringBuffer sql = new StringBuffer(" select * from student where 1=1 and state!="+StudentStateEnum.Delete.state+" ");
        // 2-判断请求参数
        ArrayList paramList = new ArrayList();

        if(stuName!=null&&stuName.length()>0){
            sql.append(" and stuname like ? ");
            paramList.add("%"+stuName+"%");
        }
        if(stuNo!=null&&stuNo.length()>0){
            sql.append(" and stuno=? ");
            paramList.add(stuNo);
        }
        if(sex!=-1){
            sql.append(" and sex=? ");
            paramList.add(sex);
        }

        sql.append(" limit ?,? ");
        paramList.add((pageIndex-1)*pageNum);
        paramList.add(pageNum);

        // 3-查询数据库
        resultSet = query(sql.toString(),paramList);
        if(resultSet==null){
            return null;
        }
        ArrayList studentList = new ArrayList();
        try {
            while (resultSet.next()) {
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setgId(resultSet.getInt("gid"));
                studentList.add(student);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
            return studentList;
        }
    }

    @Override
    public int getTotalDataNumber(String stuName, String stuNo, int sex) {
        StringBuffer sql = new StringBuffer("select count(*) from student where 1=1 and state!="+StudentStateEnum.Delete.state+" ") ;
        ArrayList paramList = new ArrayList();
        if(stuName!=null&&stuName.length()>0){
            sql.append(" and stuname like ? ");
            paramList.add("%"+stuName+"%");
        }
        if(stuNo!=null&&stuNo.length()>0){
            sql.append(" and stuNo = ? ");
            paramList.add(stuNo);
        }
        if(sex!=-1){
            sql.append(" and sex = ? ");
            paramList.add(sex);
        }
        System.out.println(sql);
        resultSet = query(sql.toString(),paramList);
        int totalDataNumber = 0;
        if(resultSet==null){
            return totalDataNumber;
        }
        try {
            while(resultSet.next()){
                totalDataNumber=resultSet.getInt(1);
                System.out.println(totalDataNumber);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
            return totalDataNumber;
        }
    }

    @Override
    public int addStudentInfo(Student student) {
        String sql = "insert into student values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ArrayList paramList = new ArrayList();
        paramList.add(student.getStuId());
        paramList.add(student.getStuName());
        paramList.add(student.getStuNo());
        paramList.add(student.getSex());
        paramList.add(student.getPhone());
        paramList.add(student.getEmail());
        paramList.add(student.getRegistered());
        paramList.add(student.getAddress());
        paramList.add(student.getProfession());
        paramList.add(student.getIdNumber());
        paramList.add(student.getPolitics());
        paramList.add(student.getRegdate());
        paramList.add(student.getState());
        paramList.add(student.getIntroduction());
        paramList.add(student.getgId());
        int influenceRow = update(sql,paramList);
        return influenceRow;
    }

    @Override
    public Student displayBeforeModifyStudentInfo(String stuNo){
        String sql = "select * from student where stuno = ?";
        ArrayList paramList = new ArrayList();
        paramList.add(stuNo);
        resultSet = query(sql,paramList);
        Student student = new Student();
        try {
            while (resultSet.next()) {
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(stuNo);
                student.setgId(resultSet.getInt("gid"));
                student.setSex(resultSet.getInt("sex"));
                student.setEmail(resultSet.getString("email"));
                student.setPhone(resultSet.getString("phone"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setPolitics(resultSet.getString("politics"));
                student.setIdNumber(resultSet.getString("idNumber"));
                student.setProfession(resultSet.getString("profession"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setState(resultSet.getInt("state"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setStuId(resultSet.getInt("stuid"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int modifyStudentInfo(Student student){
        String sql = "update student set stuno=? , stuname=?, gid=?, sex=? , phone=?, email=?, regdate=?, registered=?, politics=?, address=?," +
                "idnumber=?, profession=?, introduction=? where stuid=?";

        ArrayList paramList = new ArrayList();
        paramList.add(student.getStuNo());
        paramList.add(student.getStuName());
        paramList.add(student.getgId());
        paramList.add(student.getSex());
        paramList.add(student.getPhone());
        paramList.add(student.getEmail());
        paramList.add(student.getRegdate());
        paramList.add(student.getRegistered());
        paramList.add(student.getPolitics());
        paramList.add(student.getAddress());
        paramList.add(student.getIdNumber());
        paramList.add(student.getProfession());
        paramList.add(student.getIntroduction());
        paramList.add(student.getStuId());

        int influenceRow = update(sql,paramList);
        closeAll();
        return influenceRow;
    }

    @Override
    public int deleteStudentInfo(int stuId) {
        String sql = "update student set state = ? where stuid = ?";
        ArrayList paramList = new ArrayList();
        paramList.add(StudentStateEnum.Delete.state);
        paramList.add(stuId);
        int influenceRow = update(sql,paramList);
        closeAll();
        return influenceRow;
    }
}
